using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;




namespace PShop
{
    

    public partial class Form_sales : Form
   {
       OleDbConnection con = null;
       OleDbCommand cmd = null;
       OleDbDataReader reader;

        

        public Form_sales()
        {
            InitializeComponent();
            con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/PShop.mdb");

        }

        //My functions

        private void populateProductComboBox() 
        {
            con.Open();
            cmd = new OleDbCommand("select product_id from product", con);
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                
                comboBox_productID.Items.Add(reader.GetValue(0).ToString());
                

            }
            con.Close();
        }




        private void Form_sales_Load(object sender, EventArgs e)
        {
            
            // TODO: This line of code loads data into the 'dataSetPShop.product' table. You can move, or remove it, as needed.
            //this.productTableAdapter.Fill(this.dataSetPShop.product);
            populateProductComboBox();
            con.Open();
            cmd = new OleDbCommand("select max(sales_id) from sales", con);
            reader = cmd.ExecuteReader();
            reader.Read();
            label_currentOrderID.Text = (Int32.Parse(reader.GetValue(0).ToString()) + 1).ToString();
            con.Close();

        }

        private void comboBox_productID_SelectedIndexChanged(object sender, EventArgs e)
        {
            con.Open();
            //MessageBox.Show(comboBox_productID.SelectedItem.ToString());
            cmd = new OleDbCommand("SELECT product_stock FROM product WHERE product_id = '" + comboBox_productID.SelectedItem.ToString() + "'", con);
            reader = cmd.ExecuteReader();
            reader.Read();
            numericUpDown_quantity.Maximum = Int32.Parse(reader.GetValue(0).ToString());
            //MessageBox.Show(numericUpDown_quantity.Maximum.ToString());
            con.Close();
            
        }

        private void button_addProductForSale_Click(object sender, EventArgs e)
        {
            
            con.Open();
            cmd = new OleDbCommand("select product_id, product_name, product_salling_price from product where (product_id = '" + comboBox_productID.Text + "')", con);
            reader = cmd.ExecuteReader();
            reader.Read();
            String[] tempArray = new String[4];
            tempArray[0] = reader.GetValue(0).ToString();
            tempArray[1] = numericUpDown_quantity.Value.ToString();
            tempArray[2] = reader.GetValue(2).ToString();
            tempArray[3] = (Int32.Parse(tempArray[2]) * numericUpDown_quantity.Value).ToString(); 
            dataGridView_listOfSaleItems.Rows.Add(tempArray);
            con.Close();
            comboBox_productID.Text = "";
            numericUpDown_quantity.Value = 1;



        }

        private void button_removeProduct_Click(object sender, EventArgs e)
        {
            if (dataGridView_listOfSaleItems.Rows.Count == 1)
                MessageBox.Show("Sales List item is empty");
            for (int rowCounter = 0; rowCounter < dataGridView_listOfSaleItems.SelectedRows.Count; )
            {
                dataGridView_listOfSaleItems.Rows.Remove(dataGridView_listOfSaleItems.SelectedRows[rowCounter]);
                dataGridView_listOfSaleItems.Update();
            }
        }

        private void button_sell_Click(object sender, EventArgs e)
        {
            int nextSalesID = int.Parse(label_currentOrderID.Text);
            
            //MessageBox.Show(nextSalesID.ToString());
            String productID;
            int productQuantity;
            double productPrice;
            con.Open();
            //MessageBox.Show("number of row" + dataGridView_listOfSaleItems.Rows.Count.ToString());
            for (int rowCounter = 0; rowCounter < dataGridView_listOfSaleItems.Rows.Count - 1; rowCounter++)
            {
                productID = dataGridView_listOfSaleItems.Rows[rowCounter].Cells["product_id"].Value.ToString();
                productQuantity = Int32.Parse(dataGridView_listOfSaleItems.Rows[rowCounter].Cells["product_quantity"].Value.ToString());
                productPrice = double.Parse(dataGridView_listOfSaleItems.Rows[rowCounter].Cells["product_salling_price"].Value.ToString());
                String command = "insert into sales values(" + nextSalesID + ",'" + productID + "'," + productQuantity + ",'" + DateTime.Now.ToString() + "'," + productPrice + ")";
                cmd = new OleDbCommand(command, con);
                cmd.ExecuteNonQuery();
                cmd = new OleDbCommand("select product_stock from product where product_id = '" + productID + "'", con);
                reader = cmd.ExecuteReader();
                reader.Read();
                int currentStock = Int32.Parse(reader.GetValue(0).ToString()) - productQuantity;
                //MessageBox.Show(currentStock.ToString());
                cmd = new OleDbCommand("update product set product_stock = " + currentStock + " where product_id = '" + productID + "'", con);
                cmd.ExecuteNonQuery();
                
            }

            con.Close();
            dataGridView_listOfSaleItems.Rows.Clear();
                    label_currentOrderID.Text = "";


        }

       
    }
}